﻿CREATE procedure [dbo].[spReportApplicationBrazilLaggedProjects]
	@yearPosition int,
	@date datetime
AS

DECLARE @year int
SET @year = YEAR(@date) + @yearPosition

DECLARE @previousYear int
SET @previousYear = @year - 1

DECLARE @query varchar(max) , @query2 varchar(max) 

SET @query = '
	declare @lastDatePrevious datetime
	select @lastDatePrevious = MAX(ReferenceDate) from ApplicationBookOfWork' + CONVERT(varchar(4), @previousYear) + '

	select DISTINCT ' + CONVERT(varchar(4), @year) + ' as [Period Year], AccountLevel1 as [Account Level 1], AccountLevel2 as [Account Level 2]
	, AccountLevel3 as [Account Level 3], InitiativeId as [Initiative Id], InitiativeName as [Initiative Name]
	, ProgramId as [Program Id], ProgramName as [Program Name], ProjectId as [Project Id], ProjectName as [Project Name]
	, ProgramManager as [Program Manager], ProgramStartDate as [Program Start Date], ProgramEndDate as [Program End Date]
	, ProjectStatus as [Project Status], ProjectStartDate as [Project Start Date], ProjectEndDate as [Project End Date]
	, ApplicationId as [Application Id], ApplicationDetail.Name as [Application Name], ApplicationDetail.RetirementDate as [Application Retirement Date]
	, ProjectManager as [Project Manager], BillingClientGocCode as [Billing Client Goc Code], Client3Name as [Client 3 Name]
	, Client4Name as [Client 4 Name], Client5Name as [Client 5 Name], Client6Name as [Client 6 Name], Client7Name as [Client 7 Name]
	, Client8Name as [Client 8 Name], Client9Name as [Client 9 Name], Client10Name as [Client 10 Name], ClientGocCode as [Client Goc Code]
	, ClientGOC.ManagedGeography as [Client Geography Code], ClientGOC.ManagedGeographyDescription as [Client Geography Description]
	, System5Name as [System 5 Name], System6Name as [System 6 Name], System7Name as [System 7 Name], System8Name as [System 8 Name]
	, System9Name as [System 9 Name], System10Name as [System 10 Name], SystemGOCCode as [System Goc Code]
	, SystemGOC.ManagedGeography as [System Geography Code], SystemGOC.ManagedGeographyDescription as [System Geography Description]
	, SystemRegion as [System Region], LOB as [LOB], RuntheBankBuildtheBank as [Run the Bank Build the Bank]
	, ProgramProjectLinkIndicator as [Program Project Link Indicator], WorkTypeFinancials as [Work Type Financials]
	, InitiativeInvestimentCategory as [Init Invest Category], ProgramInvestimentCategory as [Prog Invest Category]
	, ProgramCapitalizedIndicator as [Prog Capind], ProgramStatus as [Program Status], ProjectCapitalization as [Project Capitalization]
	, BowFlag as [Bow Flag], BowFlag2 as [Bow Flag2], ProjectType as [Project Type], ProgramCarryoverIndicator as [Program Carryover Indicator]
	, TaxonomyName as [Taxonomy Name], CURate as [Cu Rate], FirstOfProgramDesc as [First of Program Desc], LobParent as [Lob Parent]
	, AdjustedFlag as [Adjusted Flag], January as [January Bow], February as [February Bow], March as [March Bow], April as [April Bow]
	, May as [May Bow], June as [June Bow], July as [July Bow], August as [August Bow], September as [September Bow], October as [October Bow]
	, November as [November Bow], December as [December Bow], YearToDate as [Year To date Bow], FullYear as [Full Year Bow] 
	, IsNull(case AccountLevel1 When ''Cap'' then 0 else January end, 0) as [System January Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then January else February end, 0) as [System February Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then February else March end, 0) as [System March Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then March else April end, 0) as [System April Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then April else May end, 0) as [System May Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then May else June end, 0) as [System June Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then June else July end, 0) as [System July Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then July else August end, 0) as [System August Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then August else September end, 0) as [System September Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then September else October end, 0) as [System October Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then October else November end, 0) as [System November Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then November else December end, 0) as [System December Bow]
	, IsNull(case AccountLevel1 When ''Cap'' then 0 else January end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then January else February end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then February else March end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then March else April end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then April else May end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then May else June end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then June else July end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then July else August end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then August else September end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then September else October end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then October else November end, 0) +
		IsNull(case AccountLevel1 When ''Cap'' then November else December end, 0) as [System Full Year Bow]
	, 0 as [Client January Bow], January as [Client February Bow]
	, February as [Client March Bow], March as [Client April Bow]
	, April as [Client May Bow], May as [Client June Bow]
	, June as [Client July Bow], July as [Client August Bow]
	, August as [Client September Bow], September as [Client October Bow]
	, October as [Client November Bow], November as [Client December Bow]
	, 0 + January + February + March + April + May +
		June + July + August + September + October+ November as [Client Full Year Bow]
from ApplicationBookOfWork' + CONVERT(varchar(4), @year) + ' Application
	LEFT JOIN Application as ApplicationDetail on ApplicationDetail.Id = ApplicationId
	LEFT JOIN GOC as ClientGOC on ClientGOC.GocAttributes = ClientGocCode 
	LEFT JOIN GOC as SystemGOC on SystemGOC.GocAttributes = SystemGocCode 
Where Application.ReferenceDate = ''' + CONVERT(varchar(20), @date) + '''' 
	
SET @query2 = ' UNION 
select DISTINCT ' + CONVERT(varchar(4), @year) + ' as [Period Year], AccountLevel1 as [Account Level 1], AccountLevel2 as [Account Level 2]
	, AccountLevel3 as [Account Level 3], InitiativeId as [Initiative Id], InitiativeName as [Initiative Name]
	, ProgramId as [Program Id], ProgramName as [Program Name], ProjectId as [Project Id], ProjectName as [Project Name]
	, ProgramManager as [Program Manager], ProgramStartDate as [Program Start Date], ProgramEndDate as [Program End Date]
	, ProjectStatus as [Project Status], ProjectStartDate as [Project Start Date], ProjectEndDate as [Project End Date]
	, ApplicationId as [Application Id], ApplicationDetail.Name as [Application Name], ApplicationDetail.RetirementDate as [Application Retirement Date]
	, ProjectManager as [Project Manager], BillingClientGocCode as [Billing Client Goc Code], Client3Name as [Client 3 Name]
	, Client4Name as [Client 4 Name], Client5Name as [Client 5 Name], Client6Name as [Client 6 Name], Client7Name as [Client 7 Name]
	, Client8Name as [Client 8 Name], Client9Name as [Client 9 Name], Client10Name as [Client 10 Name], ClientGocCode as [Client Goc Code]
	, ClientGOC.ManagedGeography as [Client Geography Code], ClientGOC.ManagedGeographyDescription as [Client Geography Description]
	, System5Name as [System 5 Name], System6Name as [System 6 Name], System7Name as [System 7 Name], System8Name as [System 8 Name]
	, System9Name as [System 9 Name], System10Name as [System 10 Name], SystemGOCCode as [System Goc Code]
	, SystemGOC.ManagedGeography as [System Geography Code], SystemGOC.ManagedGeographyDescription as [System Geography Description]
	, SystemRegion as [System Region], LOB as [LOB], RuntheBankBuildtheBank as [Run the Bank Build the Bank]
	, ProgramProjectLinkIndicator as [Program Project Link Indicator], WorkTypeFinancials as [Work Type Financials]
	, InitiativeInvestimentCategory as [Init Invest Category], ProgramInvestimentCategory as [Prog Invest Category]
	, ProgramCapitalizedIndicator as [Prog Capind], ProgramStatus as [Program Status], ProjectCapitalization as [Project Capitalization]
	, BowFlag as [Bow Flag], BowFlag2 as [Bow Flag2], ProjectType as [Project Type], ProgramCarryoverIndicator as [Program Carryover Indicator]
	, TaxonomyName as [Taxonomy Name], CURate as [Cu Rate], FirstOfProgramDesc as [First of Program Desc], LobParent as [Lob Parent]
	, AdjustedFlag as [Adjusted Flag]
	, 0 as [January Bow]
	, 0 as [February Bow]
	, 0 as [March Bow]
	, 0 as [April Bow]
	, 0 as [May Bow]
	, 0 as [June Bow]
	, 0 as [July Bow]
	, 0 as [August Bow]
	, 0 as [September Bow]
	, 0 as [October Bow]
	, 0 as [November Bow]
	, 0 as [December Bow]
	, 0 as [Year To date Bow]
	, 0 as [Full Year Bow] 
	, case previousBow.AccountLevel1 When ''Cap'' then ISNULL(previousBow.December, 0) else 0 end as [System January Bow]
	, 0 as [System February Bow]
	, 0 as [System March Bow]
	, 0 as [System April Bow]
	, 0 as [System May Bow]
	, 0 as [System June Bow]
	, 0 as [System July Bow]
	, 0 as [System August Bow]
	, 0 as [System September Bow]
	, 0 as [System October Bow]
	, 0 as [System November Bow]
	, 0 as [System December Bow]
	, case previousBow.AccountLevel1 When ''Cap'' then ISNULL(previousBow.December, 0) else 0 end as [System Full Year Bow]
	, previousBow.December as [Client January Bow], 0 as [Client February Bow]
	, 0 as [Client March Bow], 0 as [Client April Bow]
	, 0 as [Client May Bow], 0 as [Client June Bow]
	, 0 as [Client July Bow], 0 as [Client August Bow]
	, 0 as [Client September Bow], 0 as [Client October Bow]
	, 0 as [Client November Bow], 0 as [Client December Bow]
	, previousBow.December as [Client Full Year Bow]
from ApplicationBookOfWork' + CONVERT(varchar(4), @previousYear) + ' previousBow
	LEFT JOIN Application as ApplicationDetail on ApplicationDetail.Id = ApplicationId
	LEFT JOIN GOC as ClientGOC on ClientGOC.GocAttributes = previousBow.ClientGocCode 
	LEFT JOIN GOC as SystemGOC on SystemGOC.GocAttributes = previousBow.SystemGocCode 
Where previousBow.ReferenceDate = @lastDatePrevious'
	
EXEC(@query + ' ' + @query2)